﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using BHXH_NT.XML;
using Microsoft.VisualBasic;
using System.Transactions;
using BHXH_NT.Properties;
using BHXH_NT.Data;
using CrystalDecisions.CrystalReports.Engine;

namespace BHXH_NT
{
    public partial class frmCheckMaCV : Form
    {
        private string connectionString = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString;
        CheckMaCV ds = new CheckMaCV();
        public frmCheckMaCV()
        {
            InitializeComponent();
        }

        private void btnDS_Click(object sender, EventArgs e)
        {
            string queryString = "SELECT b.Madvi, a.Sobhxh, c.Ho, c.Ten,dv.DSNV  "
            + "  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh "
            + "  left join dmdonvi dv on dv.Madvi = b.Madvi,  (SELECT Sobhxh, Ma_Nte, Loaidt, "
            + "  MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE LEFT(Maqt,6)=Denthang AND LEFT(Maqt,6)<=201112"
            + "  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY Sobhxh, Ma_Nte, Loaidt) a  "
            + "  WHERE a.Sobhxh=b.Sobhxh AND a.Ma_Nte=b.Ma_Nte AND a.Loaidt=b.Loaidt AND a.Maqt=b.Maqt " 
            + "  AND LEFT(b.Maqt,6)=b.Denthang AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0  "
            + "  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc5+b.Ml<>0  and c.Macv = '' and left(b.Madvi,1) <> 'B' AND CHARINDEX('" + Settings.Default.MaTam + "',a.SOBHXH) = 0 AND dv.DSNV = '" + txtnhanvien.Text + ",'"
            + "  UNION  "
            + "  SELECT DISTINCT d.Madvi, d.Sobhxh,e.Ho, e.Ten,dv.DSNV"
            + "  FROM HSC47_DTL d LEFT JOIN HOSOCN e ON d.Sobhxh=e.Sobhxh"
            + "  left join dmdonvi dv on dv.Madvi = d.Madvi  WHERE LEFT(d.Maqt,6) "
            + "  BETWEEN  201101  AND 201112  AND d.Sobhxh NOT IN  (SELECT a.Sobhxh FROM HSC47_DTL b ,  "
            + "  (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE "
            + "  LEFT(Maqt,6)=Denthang AND LEFT(Maqt,6)<=201112  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + "  GROUP BY Sobhxh, Ma_Nte, Loaidt) a  "
            + "  WHERE a.Sobhxh=b.Sobhxh AND a.Ma_Nte=b.Ma_Nte AND a.Loaidt=b.Loaidt AND a.Maqt=b.Maqt  "
            + "  AND LEFT(b.Maqt,6)=b.Denthang AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0  "
            + "  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc5+b.Ml<>0 ) and e.Macv = '' and left(d.Madvi,1) <> 'B'  AND CHARINDEX('" + Settings.Default.MaTam + "',d.SOBHXH) = 0 AND dv.DSNV = '" + txtnhanvien.Text + ",'"
            + "  order by DSNV";

            //and left(d.madvi,2) = 'FA'
                Cursor.Current = Cursors.WaitCursor;
                ds.Tables[0].Clear();
                SqlDataAdapter dAdapt =  new SqlDataAdapter(queryString, connectionString);
                dAdapt.SelectCommand.CommandTimeout = 160;
                dAdapt.Fill(ds, "dbCheckMaCV");
                int i = 1;
                fa.Rows.Count = 1;
                using (SqlConnection connection =
                       new SqlConnection(connectionString))
                {
                    SqlCommand command = connection.CreateCommand();
                  
                    connection.Open();
                    SqlDataReader reader;
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        queryString = "exec sp_executesql N'EXEC PSCTCD @P1 , @P2 , @P3 , @P4 ',"
                         + " N'@P1 varchar(16),@P2 varchar(7),@P3 varchar(6),@P4 varchar(6)', "
                         + " '" + dr["sobhxh"] + "', '" + dr["Madvi"] + "', '200901', '201112' ";
                        command.CommandText = queryString;
                        reader = command.ExecuteReader();
                        reader.Read();
                        if (!reader.HasRows)
                        {
                            fa.Rows.Count = fa.Rows.Count + 1;
                            fa.Rows[i]["STT"] = i;
                            fa.Rows[i]["Madvi"] = dr["Madvi"];
                            fa.Rows[i]["sobhxh"] = dr["sobhxh"]; ;
                            fa.Rows[i]["Ho"] = dr["Ho"];
                            fa.Rows[i]["Ten"] = dr["Ten"];
                            fa.Rows[i]["dsnv"] = dr["dsnv"];
                            i++;

                        }
                        else
                        {
                            dr.Delete(); 
                        }
                        reader.Close();
                    }
                }
                ds.Tables[0].AcceptChanges();
                Cursor.Current = Cursors.Default;
                
        }

        private void btnHoKhau_Click(object sender, EventArgs e)
        {
            string queryString = "SELECT C.Madvi,a.Sobhxh,c.Ho, c.Ten, dv.DSNV  FROM HSC47_DTL b LEFT JOIN "
            + " HOSOCN c ON b.Sobhxh=c.Sobhxh left join dmdonvi dv on dv.Madvi = c.Madvi,"   
            + " (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL WHERE "
            + " LEFT(Maqt,6)=Denthang AND  LEFT(Maqt,6)<=201204  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + " GROUP BY Sobhxh, Loaidt, Ma_Nte) a   WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
            + " AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang "
            + " AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt<>0 AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 AND "
            + " diachiHK = '' and dv.DSNV = '" + txtnhanvien.Text + ",' AND CHARINDEX('" + Settings.Default.MaTam + "',a.SOBHXH) = 0   and left(c.madvi,1) <> 'B'"
            + " order by dv.DSNV, c.madvi,a.sobhxh";

            //and left(d.madvi,2) = 'FA'
            Cursor.Current = Cursors.WaitCursor;
            ds.Tables[0].Clear();
            SqlDataAdapter dAdapt = new SqlDataAdapter(queryString, connectionString);
            dAdapt.SelectCommand.CommandTimeout = 160;
            dAdapt.Fill(ds, "dbCheckMaCV");
            int i = 1;
            fa.Rows.Count = 1;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                   
                        fa.Rows.Count = fa.Rows.Count + 1;
                        fa.Rows[i]["STT"] = i;
                        fa.Rows[i]["Madvi"] = dr["Madvi"];
                        fa.Rows[i]["sobhxh"] = dr["sobhxh"]; ;
                        fa.Rows[i]["Ho"] = dr["Ho"];
                        fa.Rows[i]["Ten"] = dr["Ten"];
                        fa.Rows[i]["dsnv"] = dr["dsnv"];
                        i++;
                       
            }
                    
            
            Cursor.Current = Cursors.Default;
        }

        private void btnInso_Click(object sender, EventArgs e)
        {
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("rptCheckNV.rpt");
            cr.SetDataSource(ds);
            f.crpView.ReportSource = cr;
            f.Show();
        }

      
    }
}
